import json
import mysql.connector
from mysql.connector import Error


def connect_to_mysql():
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='bmap_poi',
            user='root',
            password='root'
        )
        if connection.is_connected():
            return connection
    except Error as e:
        print(f"Error connecting to MySQL: {e}")
    return None


def insert_admin_info(cursor, admin_info):
    query = """
    INSERT INTO admin_info (area_id, area_name, city_id, city_name, province_id, province_name, town_id, town_name)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """
    values = (
        admin_info.get('area_id'), admin_info.get('area_name'),
        admin_info.get('city_id'), admin_info.get('city_name'),
        admin_info.get('province_id'), admin_info.get('province_name'),
        admin_info.get('town_id'), admin_info.get('town_name')
    )
    cursor.execute(query, values)
    return cursor.lastrowid


def insert_api_admin_info(cursor, api_admin_info):
    query = """
    INSERT INTO api_admin_info (area_code, area_name, city_code, city_name, prov_code, prov_name)
    VALUES (%s, %s, %s, %s, %s, %s)
    """
    values = (
        api_admin_info.get('area_code'), api_admin_info.get('area_name'),
        api_admin_info.get('city_code'), api_admin_info.get('city_name'),
        api_admin_info.get('prov_code'), api_admin_info.get('prov_name')
    )
    cursor.execute(query, values)
    return cursor.lastrowid


def insert_poi_info(cursor, poi, admin_info_id, api_admin_info_id):
    query = """
    INSERT INTO poi_info (
        uid, primary_uid, name, addr, address_norm, tel, x, y, dipointx, dipointy,
        navi_x, navi_y, dis, geo, status, acc_flag, addr_hot_percent, biz_type,
        catalogid, new_catalog_id, city_id, city_name, area, area_name, cqr, detail,
        di_tag, dist2route, dist2start, ext_type, f_flag, flag_type, gbrank_score,
        geo_type, hit_tag_press, indoor_pano, is_nearnest_nat, is_top_nat, ismodified,
        navi_update_time, pano, poitype, poi_click_num, poi_profile, prio_flag,
        profile_geo, qu_brandid, route_flag, std_tag, std_tag_id, storage_src, tag,
        top1_reinforce, ty, view_type, street_id, father_son, brand_id,
        admin_info_id, api_admin_info_id, query_guide, address_color_shows,
        title_color_shows, show_tag, cla, ext_display
    ) VALUES (
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s
    )
    """
    values = (
        poi.get('uid'), poi.get('primary_uid'), poi.get('name'), poi.get('addr'),
        poi.get('address_norm'), poi.get('tel'), poi.get('x'), poi.get('y'),
        poi.get('dipointx'), poi.get('dipointy'), poi.get('navi_x'), poi.get('navi_y'),
        poi.get('dis'), poi.get('geo'), poi.get('status'), poi.get('acc_flag'),
        poi.get('addr_hot_percent'), poi.get('biz_type'), poi.get('catalogid'),
        poi.get('new_catalog_id'), poi.get('city_id'), poi.get('city_name'),
        poi.get('area'), poi.get('area_name'), poi.get('cqr'), poi.get('detail'),
        poi.get('di_tag'), poi.get('dist2route'), poi.get('dist2start'),
        poi.get('ext_type'), poi.get('f_flag'), poi.get('flag_type'),
        poi.get('gbrank_score'), poi.get('geo_type'), poi.get('hit_tag_press'),
        poi.get('indoor_pano'), poi.get('is_nearnest_nat'), poi.get('is_top_nat'),
        poi.get('ismodified'), poi.get('navi_update_time'), poi.get('pano'),
        poi.get('poitype'), poi.get('poi_click_num'), poi.get('poi_profile'),
        poi.get('prio_flag'), poi.get('profile_geo'), poi.get('qu_brandid'),
        poi.get('route_flag'), poi.get('std_tag'), poi.get('std_tag_id'),
        poi.get('storage_src'), poi.get('tag'), poi.get('top1_reinforce'),
        poi.get('ty'), poi.get('view_type'), poi.get('street_id'),
        poi.get('father_son'), '', admin_info_id, api_admin_info_id,
        json.dumps(poi.get('query_guide')), json.dumps(poi.get('address_color_shows')),
        json.dumps(poi.get('title_color_shows')), json.dumps(poi.get('show_tag')),
        json.dumps(poi.get('cla')), json.dumps(poi.get('ext_display'))
    )
    print(values)
    cursor.execute(query, values)


def check_poi_exists(cursor, uid):
    query = "SELECT COUNT(*) FROM poi_info WHERE uid = %s"
    cursor.execute(query, (uid,))
    count = cursor.fetchone()[0]
    return count > 0


def process_poi_data(poi_data):
    connection = connect_to_mysql()
    if connection is None:
        return

    try:
        cursor = connection.cursor()

        for poi in poi_data:
            if check_poi_exists(cursor, poi['uid']):
                print(f"POI with uid {poi['uid']}({poi['name']}) already exists. Skipping.")
                continue

            # Insert admin_info
            admin_info_id = insert_admin_info(cursor, poi['admin_info'])

            # Insert api_admin_info
            api_admin_info_id = insert_api_admin_info(cursor, poi['api_admin_info'])

            # Insert poi_info
            insert_poi_info(cursor, poi, admin_info_id, api_admin_info_id)

        connection.commit()
        print("Data inserted successfully")

    except Error as e:
        print(f"Error: {e}")
        connection.rollback()

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")


if __name__ == "__main__":
    # 假设您的JSON数据存储在一个名为 'poi_data.json' 的文件中
    with open('poi_data.json', 'r', encoding='utf-8') as file:
        poi_data = json.load(file)

    process_poi_data(poi_data)